package com.rec.service.impl;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.transaction.Transactional;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import com.rec.entity.Students;
import com.rec.repository.StudentRepository;
import com.rec.service.StudentsService;
@Service
@Transactional
public class StudentsServiceImpl implements StudentsService {
	@Autowired
	private StudentRepository r;
	@Override
	public Map<String, Object> all(Map<String, Object> params) {
		Specification<Students> spec = getSpec(params);
		// 创建分页对象
		Integer page = (Integer) params.get("page");
		Integer limit = (Integer) params.get("limit");
		Pageable pa = PageRequest.of(page, limit);
		Page<Students> result = r.findAll(spec,pa);
		List<Students> data = result.getContent();
		long count = result.getTotalElements();
		if(data.size()>0) {
			params.put("code", 0);
			params.put("data", data);
			params.put("count", count);
			
		} else {
			params.put("code", 1);
			params.put("msg", "没有数据！");
		}
		return params;
	}
	private Specification<Students> getSpec(Map<String, Object> params) {
		return new Specification<Students>() {

			@Override
			public Predicate toPredicate(Root<Students> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				Predicate pre = cb.conjunction();
				List<Expression<Boolean>> sqlList = pre.getExpressions();
				if(!StringUtils.isEmpty(params.get("cardno"))) {
					sqlList.add(cb.equal(root.get("cardno"),params.get("cardno")));
				}
				if(!StringUtils.isEmpty(params.get("name"))) {
					sqlList.add(cb.like(root.get("name"),"%"+params.get("name")+"%"));
				}
				if(!StringUtils.isEmpty(params.get("sex"))) {
					sqlList.add(cb.equal(root.get("sex"),params.get("sex")));
				}
				if(!StringUtils.isEmpty(params.get("memberid"))) {
					sqlList.add(cb.equal(root.get("memberid"),params.get("memberid")));
				}
				if(!StringUtils.isEmpty(params.get("status"))) {
					sqlList.add(cb.equal(root.get("status"),params.get("status")));
				}
				return pre;
			}
			
		};
	}
	@Override
	public Map<String, Object> add(Map<String, Object> params) {
		Students students = (Students) params.get("Stu");
		Students b = r.save(students);
		if(b!=null) {
			params.put("code", 0);
			params.put("msg", "新增成功！");
		}else {
			params.put("code", 1);
			params.put("msg", "新增失败！");
		}
		return params;
	}
	@Override
	public Map<String, Object> update(Map<String, Object> params) {
			Integer id = (Integer) params.get("id");
			String name = (String) params.get("name");
			String sex = (String) params.get("sex");
			Integer memberid = (Integer) params.get("memberid");
			Integer status = (Integer) params.get("status");
			Integer n = r.updateStu(id,name,sex,memberid,status);
			if(n>0) {
				params.put("code", 0);
				params.put("msg", "修改成功！");
			}else {
				params.put("code", 1);
				params.put("msg", "修改失败！");
			}
		return params;
	}
	@Override
	public Map<String, Object> delete(Map<String, Object> params) {
		   Integer id  = (Integer) params.get("delid");
		   Integer n = r.deleteStu(id);
			if(n>0) {
				params.put("code", 0);
				params.put("msg", "删除成功！");
			}else {
				params.put("code", 1);
				params.put("msg", "删除失败！");
			}
		return params;
	}
	@Override
	public void addStudent(Students stu) {
		r.save(stu);
	}
	@Override
	public Integer download(List<Integer> sid) throws IOException {
		List<Students> list = r.findAllById(sid);
		System.out.println(sid);
			XSSFWorkbook Workbook = new XSSFWorkbook();
			XSSFSheet sheet = Workbook.createSheet("学生信息");
			XSSFRow row = sheet.createRow(0);
			row.createCell(0).setCellValue("ID");
			row.createCell(1).setCellValue("学号");
			row.createCell(2).setCellValue("姓名");
			row.createCell(3).setCellValue("院系");
			row.createCell(4).setCellValue("性别");
			row.createCell(5).setCellValue("状态");
			for(int i =0;i<list.size();i++) {
				XSSFRow rowl = sheet.createRow(i+1);
				rowl.createCell(0).setCellValue(list.get(i).getId());
				System.out.println(list.get(i).getId());
				rowl.createCell(1).setCellValue(list.get(i).getCardno());
				System.out.println(list.get(i).getCardno());
				rowl.createCell(2).setCellValue(list.get(i).getName());
				System.out.println(list.get(i).getName());
				rowl.createCell(3).setCellValue(list.get(i).getM().getDepartment());
				System.out.println(list.get(i).getM().getDepartment());
				rowl.createCell(4).setCellValue(list.get(i).getSex());
				System.out.println(list.get(i).getSex());
				rowl.createCell(5).setCellValue(list.get(i).getStatus());
				System.out.println(list.get(i).getStatus());
			}
			Integer i = 0;
			if(sheet.getLastRowNum()>0) {
				i = sheet.getLastRowNum();
				File file = new File("D:/学生信息.xlsx");
				// 将Excel存盘
				FileOutputStream stream = new FileOutputStream(file);
				Workbook.write(stream);
				System.out.println("工作表行号："+sheet.getLastRowNum());
				stream.flush();
				stream.close();
				Workbook.close();
			}
			return i;
	}
	@Override
	public Students findCardnoExist(String cardno) {
		return r.findByCardno(cardno);
	}

	
	
	
	
	
	
	
	
	
	
}
